CS157a Spring 2002Practice Midterm 2

The practice midterm is below. Here are some facts about the actual midterm: (a) The midterm will be in class . (b) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (c) You should bring photo ID. (d) There will be more than one version of the test. Each version will be of comparable difficulty. (e) If your cell-phone or beeper goes off you will be excused from the test at that point and graded on what you have done till your excusal. (f) One problem (less typos) on the actual test will be from the practice test.

1. Give a short definition of each of the following terms:

   (a) bulk transfer rate
   (b) data striping
   (c) shadowing

2. Suppose 10000 Employee records are stored in a file ordered by SSN.
   The block size for the file is 2048 bytes and each record is 112 bytes.
   The records are stored in an unspanned fashion. Calculate:

   (a) The blocking factor for the Employee records.
   (b) The number of blocks in the file.
   (c) Average number of block accesses to look up a record using an SSN.

3. Suppose we have an external hash table with 3 buckets and each bucket
   can hold two records. Show after each step what the hash table would look
   like as records with the following key values:  
 
   7, 3, 5, 4, 0, 10  

   are loaded into the hash table using h(K) = K mod 3.

4. Explain the difference between a sparse index and a dense index.
   Which of the following indices is which: (a) a primary index, 
   (b) a clustering index, (c) a secondary index on a key attribute.

5. Suppose the blocking factor for index entries is 50 entries/block. 
   How many first many first level entries can a
   multilevel index with 3 levels have if the top level consists of one block?

6. Calculate p and p_leaf for a B+ - tree given that a block
   has 2048 bytes, the search key field is 7 bytes long, a record pointer is
   8 bytes long and a block pointer is 7 bytes long.

7. Define the following Relational Model concepts:
   (a) first normal form assumption
   (b) candidate key
   (c) cascading delete operation


8. Show the following two sets of relational algebra operations are 
   of equivalent expressive power: 
   { select, projection, union, difference, cartesian product} and 
   { projection, join, union, difference }   

9. Given a relational algebra expression which returns the 
   name of each Department, together with the number of
   Employee's who work in that department. 

10. Give the SQL commands to do each of the following:
   (a) Retrieve the FNAME and LNAME of all Employee's who work
       on Project X and whose SALARY is greater than 100000.
   (b) All columns of all Employee's who are in the MATH Department
       together with all columns of all Employee's who are in the 
       CS Department.
   (c) Insert the tuple ( 1, 2, 3) into the table T.

   For this problem recall the Company DB from the book. This DB has the tables:

   Employee(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX SALARY, SUPERSSN DNO), 
   Works\_On(ESSN, PNO, HOURS), 

   Project(PNAME, PNUMBER, PLOCATION, DNUM), 

   and Department(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE).